﻿using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using System.Web;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using Web;

namespace DataLook.App_Start
{
	public class NPOIEXcelHelper
	{
		 
			/// <summary>  
			/// 将excel导入到datatable  
			/// </summary>  
			/// <param name="filePath">excel路径</param>  
			/// <param name="isColumnName">第一行是否是列名</param>  
			/// <returns>返回datatable</returns>  
			public static DataTable ExcelToDataTable(string filePath, bool isColumnName)
			{
				DataTable dataTable = null;
				FileStream fs = null;
				DataColumn column = null;
				DataRow dataRow = null;
				IWorkbook workbook = null;
				ISheet sheet = null;
				IRow row = null;
				ICell cell = null;
				int startRow = 0;
				try
				{
					using (fs = File.OpenRead(filePath))
					{
						// 2007版本  
						if (filePath.IndexOf(".xlsx") > 0)
							workbook = new XSSFWorkbook(fs);
						// 2003版本  
						else if (filePath.IndexOf(".xls") > 0)
							workbook = new HSSFWorkbook(fs);
						if (workbook != null)
						{
							sheet = workbook.GetSheetAt(0);//读取第一个sheet，当然也可以循环读取每个sheet  
							dataTable = new DataTable();
							if (sheet != null)
							{
								int rowCount = sheet.LastRowNum;//总行数  
								if (rowCount > 0)
								{
									IRow firstRow = sheet.GetRow(0);//第一行  
									int cellCount = firstRow.LastCellNum;//列数  

									//构建datatable的列  
									if (isColumnName)
									{
										startRow = 1;//如果第一行是列名，则从第二行开始读取  
										for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
										{
											cell = firstRow.GetCell(i);
											if (cell != null)
											{
												if (cell.StringCellValue != null)
												{
													column = new DataColumn(cell.StringCellValue);
													dataTable.Columns.Add(column);
												}
											}
										}
									}
									else
									{
										for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
										{
											column = new DataColumn("column" + (i + 1));
											dataTable.Columns.Add(column);
										}
									}

									//填充行  
									for (int i = startRow; i <= rowCount; ++i)
									{
										row = sheet.GetRow(i);
										if (row == null) continue;

										dataRow = dataTable.NewRow();
										for (int j = row.FirstCellNum; j < cellCount; ++j)
										{
											cell = row.GetCell(j);
											if (cell == null)
											{
												dataRow[j] = "";
											}
											else
											{
												//CellType(Unknown = -1,Numeric = 0,String = 1,Formula = 2,Blank = 3,Boolean = 4,Error = 5,)  
												switch (cell.CellType)
												{
													case CellType.Blank:
														dataRow[j] = "";
														break;
													case CellType.Numeric:
														short format = cell.CellStyle.DataFormat;
														//对时间格式（2015.12.5、2015/12/5、2015-12-5等）的处理  
														if (format == 14 || format == 31 || format == 57 || format == 58)
															dataRow[j] = cell.DateCellValue;
														else
															dataRow[j] = cell.NumericCellValue;
														break;
													case CellType.String:
														dataRow[j] = cell.StringCellValue;
														break;
												}
											}
										}
										dataTable.Rows.Add(dataRow);
									}
								}
							}
						}
					}
					return dataTable;
				}
				catch (Exception ex)
				{
					if (fs != null)
					{
						fs.Close();
					}
					LogHelper.Info("将excel导入到datatable，文件异常，异常信息：" + ex.Message);
					return null;
				}
			}

			/// <summary>
			/// NPOI组件导出Excel共用方法
			/// </summary>
			/// <param name="fileName">文件名称</param>
			/// <param name="hssfworkbook"></param>
			/// <param name="sheets"></param>
			public static void NPOIExport(string fileName, HSSFWorkbook hssfworkbook, List<HSSFSheet> sheets)
			{
				using (MemoryStream ms = new MemoryStream())
				{
					hssfworkbook.Write(ms);
					ms.Flush();
					ms.Position = 0;
					sheets.ForEach(a =>
					{
						a = null;
					});
					hssfworkbook = null;

					byte[] data = ms.ToArray();

					//客户端保存
					HttpResponse response = System.Web.HttpContext.Current.Response;
					response.Clear();
					response.Charset = "UTF-8";
					HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
					response.ContentType = "application/vnd-excel";//"application/vnd.ms-excel";

					//string encodeFileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8).Replace("+", " ");
					string encodeFileName = HttpUtility.UrlEncode(fileName).Replace("+", " ");
					System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + encodeFileName));// Server.UrlEncode(fileName)
					System.Web.HttpContext.Current.Response.BinaryWrite(data);
					HttpContext.Current.Response.End();
				}
			}

			/// <summary>
			/// DataTable导出到Excel文件  
			/// </summary>
			/// <param name="dtSource">源DataTable</param>
			/// <param name="strHeaderText">表头文本</param>
			/// <param name="FileName">保存位置</param>
			public static void DataTableToExcel(DataTable dt, string FileName)
			{
				var workbook = new XSSFWorkbook();

				string sheetName = "sheet1";
				ISheet sheet = workbook.CreateSheet(sheetName);
				IRow dataRow = sheet.CreateRow(0);
				foreach (DataColumn column in dt.Columns)
				{
					dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
				}


				//填充内容  
				for (int i = 0; i < dt.Rows.Count; i++)
				{
					dataRow = sheet.CreateRow(i + 1);
					for (int j = 0; j < dt.Columns.Count; j++)
					{
						dataRow.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
					}
				}

				workbook.Write(new FileStream(FileName, FileMode.Create, FileAccess.Write));
			}
		}
	} 